import mysql.connector
import requests

mydb = mysql.connector.connect(
    host='172.16.33.205',
    user='game_admin',
    password='bHYACbxgM2gWHnF6Il1nX8VnNEfb51KY',
    port='3307',
    database='ad_report'
)

# 获取游标
cursor = mydb.cursor()

# 素材管理——>数据效果（数据核对）

url = 'http://uat-ad-tracking-api-sh.bilibili.co/stat/date/group?game_id=3992&start_date=2021-01-01&end_date=2021-09-30&is_display_ineffective=2&tab_type=2&channel_id=1&group_name=&page_no=1&page_size=20&sort=&sort_type=0&request-id=BXofRFkla2BdfwFYuedcLVgDq3TCeR1r'
header={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36',
        'Cookie': '_AJSESSIONID=2ee7ac401cd06f2ecb5fe33d9a0b0a7c; username=zhangyu16'}
r = requests.get(url,headers = header)
result = r.json()


# 消耗
def cost():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_cost = cursor.fetchall()
    s1 = result_cost[0][0]
    return s1
cost()

s_cost = result['data']['total']['替换数据']
assert cost()==s_cost,'数据错误'


# 返点后消耗
def rebate_cost():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_rebate_cost = cursor.fetchall()
    s1 = result_rebate_cost[0][0]
    return s1
cost()

s_rebate_cost = result['data']['total']['替换数据']
assert rebate_cost()==s_rebate_cost,'数据错误'


# CPM
def cpm():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_cpm = cursor.fetchall()
    s1 = result_cpm[0][0]
    return s1
cpm()

s_cpm = result['data']['total']['替换数据']
assert cpm()==s_cpm,'数据错误'


# 点击率
def click():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_click = cursor.fetchall()
    s1 = result_click[0][0]
    return s1
click()

s_click = result['data']['total']['替换数据']
assert click()==s_click,'数据错误'


# 激活成本
def activate_cost():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_activate_cost = cursor.fetchall()
    s1 = result_activate_cost[0][0]
    return s1
activate_cost()

s_activate_cost = result['data']['total']['替换数据']
assert activate_cost()==s_activate_cost,'数据错误'



